﻿//该例程实现插入数据，修改数据，删除数据，数据查询等基本操作。
// 引入dmdb包
var db = require('dmdb')
var fs = require('fs')
var pool, conn

//创建连接池
async function createPool() {
  try {
    return db.createPool({
      connectString:
        'dm://BIG-SCREEN:123456@192.168.2.115:5236?clobAsString=true',
      poolMax: 10,
      poolMin: 1
    })
  } catch (err) {
    throw new Error('createPool error: ' + err.message)
  }
}

//往产品信息表插入数据
async function insertTable() {
  try {
    var sql =
      'INSERT INTO NEWTABLE (COLUMN1) ' +
      'VALUES(:1);'
    await conn.execute(sql, [
      { val: '三国演义13' }
    ])
  } catch (err) {
    throw new Error('insertTable error: ' + err.message)
  }
}

// 查询表结构
async function describeTable(tb) {
  try {
    var sql = `SELECT atc.COLUMN_NAME, atc.DATA_TYPE, atc.DATA_LENGTH, atc.CHAR_LENGTH, atc.DEFAULT_LENGTH, atc.NULLABLE, CASE WHEN ucc.CONSTRAINT_NAME  LIKE'%PK%' THEN 'PRIMARY' ELSE '' END AS "PRIMARY" FROM all_tab_columns atc LEFT OUTER JOIN all_cons_columns ucc ON(atc.table_name = ucc.table_name AND atc.COLUMN_NAME = ucc.COLUMN_NAME ) WHERE atc.OWNER=(SELECT USER FROM DUAL) AND (atc.TABLE_NAME=UPPER('${tb}') OR atc.TABLE_NAME='${tb}')ORDER BY "PRIMARY", atc.COLUMN_NAME`
    let res = await conn.execute(sql)
    // console.log(res)
    let rows = res.rows
    let metaData = res.metaData

    let fields = {}
    rows.forEach(el => {
      let field = el[0]
      let attrs = {}
      el.slice(1).forEach((value, index) => {
        let key = metaData.slice(1)[index].name
        attrs[key] = value
      })
      fields[field] = attrs
    });

    console.log(fields)
  } catch (error) {
    
  }
}
// 查询表结构
async function describeTable2(tb) {
  try {
    var sql = `SELECT atc.COLUMN_NAME, atc.DATA_TYPE, atc.DATA_LENGTH, atc.CHAR_LENGTH, atc.DEFAULT_LENGTH, atc.NULLABLE, CASE WHEN ucc.CONSTRAINT_NAME  LIKE'%PK%' THEN 'PRIMARY' ELSE '' END AS "PRIMARY" FROM all_tab_columns atc LEFT OUTER JOIN all_cons_columns ucc ON(atc.table_name = ucc.table_name AND atc.COLUMN_NAME = ucc.COLUMN_NAME ) WHERE atc.OWNER=(SELECT USER FROM DUAL) AND (atc.TABLE_NAME=UPPER('${tb}') OR atc.TABLE_NAME='${tb}')ORDER BY "PRIMARY", atc.COLUMN_NAME`
    let res = await conn.execute(sql)
    // console.log(res)
    let rows = res.rows
    let metaData = res.metaData

    const fields = []
    rows.forEach((values) => {
      const field = {}
      values.forEach((value, index) => {
        let key = metaData[index].name
        field[key] = value
      })
      fields.push(field)
    })

    console.log(fields)
  } catch (error) {
    
  }
}


async function example() {
  let close
  try {
    pool = await createPool()
    conn = await pool.getConnection()
    // var sql = 'SELECT * from newtable'
    // let res = await conn.execute(sql)
    // console.log(typeof res, res.getRows(1))
    
    // 插入
    // await insertTable()
    // conn.commit()
    // console.log('插入成功')

    // TODO
    // close = new Proxy(conn.close, {
    //   apply: function(target, thisArg, args) {
    //     console.log(thisArg, args)
    //     pool.close()
    //   }
    // })


    // conn = new Proxy(conn, {
    //   get(target, key, receiver) {
    //     if (key === 'close') {
    //       return new Proxy(target[key], {
    //         apply: async function(target, ctx, args) {
    //           // console.log('代理关闭', ctx, args)
    //           console.log('代理关闭')
    //           await pool.close()
    //         }
    //       })
    //     } else {
    //       return target[key]
    //     }
    //   }
    // })
    
    // 查询表结构
    // await describeTable2('user')


    var sql = `select * from NewTable `
    let res = await conn.execute(sql)
    console.log(res);
    
    await pool.close()
    // 临时表
    // let tb = 'tb'+ Date.now()
    // let sql = `create global temporary table ${tb} as (
    //   SELECT * from NEWTABLE
    //   )`
    //   let res = await conn.execute(sql)
    //   console.log(res)

  } catch (err) {
    console.log(err)
  } finally {
    try {
      // await pool.close()
      await conn.close()
      // close()
    } catch (err) {}
  }
}
example()